﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace DataAccessLayer
{
    public static class ContractFormulaDB
    {
        public static List<ContractFormula> GetContractFormulas()
        {
            List<ContractFormula> ContractFormulas = new List<ContractFormula>();
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * From ContractFormula";
            cmd.Connection = con;

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ContractFormula contractFormula = new ContractFormula();
                    contractFormula.Id = (int)reader["Id"];
                    contractFormula.Description = reader["Description"].ToString();
                    if (reader["MaxUsageHoursPerPeriod"] is DBNull)
                     {
                           contractFormula.MaxUsageHoursPerPeriod = 0;
                     }
                     else
                     {
                         contractFormula.MaxUsageHoursPerPeriod = (int)reader["MaxUsageHoursPerPeriod"];
                     }

                    if (reader["PeriodInMonths"] is DBNull)
                    {
                        contractFormula.PeriodInMonths = 0;
                    }
                    else
                    {
                        contractFormula.PeriodInMonths = (int)reader["PeriodInMonths"];

                    }

                    if (reader["NoticePeriodInMonths"] is DBNull)
                    {
                        contractFormula.NoticePeriodInMonths = 0;
                    }
                    else
                    {
                        contractFormula.NoticePeriodInMonths = (int)reader["NoticePeriodInMonths"];
                    }

                    if (reader["Price"] is DBNull)
                    {
                        contractFormula.Price = 0;
                    }
                    else
                    {
                        contractFormula.Price = Convert.ToDouble(reader["Price"]);
                    }
                    ContractFormulas.Add(contractFormula);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return ContractFormulas;
        }

        public static int GetContractFormulaIdByName(string ContractFormulaName)
        {
            int ContractFormulaId = 0;
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT Id From ContractFormula WHERE Description = @Description;";
            cmd.Parameters.AddWithValue("@Description", ContractFormulaName);
            cmd.Connection = con;

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    ContractFormulaId = (int)reader["Id"];
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return ContractFormulaId;
        }
    }
}
